Good day....
I see there are a few ppl here who seems to know how database replication between Access and MySql works.
This is my problem. We have an access database at the office, there are 5 tables, no relationships between them the only thing is the autonumber on each table that needs to be incremented.
It is for accounting and results purposes. Payments and results are made on the Access database in the office then it needs to be updated on the MySql database on a web server for students to view results and account information online.
I have used MyODBC which updates in real time if i am correct but they do not want it to be done like that. They want updates to be done automatically once a day.
I will be gratefull if you can help me or give me some pointers on how to do this. All help will be appreciated.
Thanks,
Cobus Minnaar
I tend to build my webapps using Access and ASP. When I have the database built and the code ready I use MySQL-Front to import the Access database into MySQL. This takes care of all the SQL to build the tables etc but it does not do the foreign key relationships - which is something I am working on at present.
MySQL-Front might be a little hard to find but try google. I think the project has been dis-continued but the import feature is very good for porting from Access to MySQL. User manaagement is also good in MySQL-Front.
The only thing I had to change in my code was the SQL for deleting records from DELETE * FROM myTable WHERE id=1 (in Access) to DELETE FROM myTable WHERE id=1.
So you say the solution to my probelm is MySQl-front...!
Do you have to go and import the new data manually or can it be done automatically once a day or so...??
I am not too fussed about the foreign keys coz the Access database they use DONT HAVE ANY and they dont want to fix or change that.
I will see if i can find it and let you knwo if I have more questions... Thanks very much once again...
Regards.
I didn't realise that you wanted to use Access as a front-end to MySQL.
I don't think this is a good idea.
I would stick with one or the other and store the database on the webserver then have webpages which retrieve info from the db for the students and then seperate web pages for admin staff to add/update info in the database.
It is best to stick with just one db type.
I had recommended MySQL-Front just to import from Access to MySQL (once not daily).
You that is my problem....
The collage doesnt want to change the MS Access database in the office... and I can not use Access on the net. So now I am forced to come up with some brilliant plan to make this work.
MyODBC works just fine, excellent actually but they dont want to do it that way.... Getting ready to kill someone...
Thanx very much for your help in any case. Appreciate it!
Have a good day!
This is your chance to shine!
If they want to stick with Access why not use ASP pages with Access on a server to show them how easy it would be for them to input and retrieve info - even easier (and cheaper) than using Access!
You can get free webspace which supports ASP and Access at www.brinkster.com
Good Luck
Thanks very much, The website has just been build with mysql and php... So that is what they want, and synchronising the two is what I need to get done.
Do you maybe know of a program which I can use (or script) to take the data from the local access db and update the records on the remote sql database...?? Anything that updates the MySql databse instead of taking the Access database and converting it into MySql. Even if i can do thins manually now I will be happy, and worry about getting it done automatically later on!
You can use the access2mysql sync which can be downloaded at http://www.soft32.com/download_13924.html?op=5.
Hope this helps...
Thanks for all the help.... I have written a program with VB so all is working very well.